package com.hotelManager.www.dao;

import com.hotelManager.www.po.Favorites;
import com.hotelManager.www.po.Shop;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Favorites DAO 类
 * 实现收藏功能的数据库操作，适应没有外键的情况。
 */
public class FavoritesDao {

    /**
     * 添加收藏记录
     * 手动验证 userId 和 shopId 的有效性。
     * @param conn 数据库连接
     * @param favorites 收藏记录
     * @return 插入是否成功
     * @throws SQLException 如果数据库操作失败
     */
    public boolean addFavorite(Connection conn, Favorites favorites) throws SQLException {
        // 验证 userId 是否存在
        String userCheckSql = "SELECT COUNT(*) FROM users WHERE id = ?";
        try (PreparedStatement userCheckStmt = conn.prepareStatement(userCheckSql)) {
            userCheckStmt.setInt(1, favorites.getUserId());
            try (ResultSet rs = userCheckStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    throw new SQLException("用户ID不存在: " + favorites.getUserId());
                }
            }
        }

        // 验证 shopId 是否存在
        String shopCheckSql = "SELECT COUNT(*) FROM shop WHERE id = ?";
        try (PreparedStatement shopCheckStmt = conn.prepareStatement(shopCheckSql)) {
            shopCheckStmt.setInt(1, favorites.getShopId());
            try (ResultSet rs = shopCheckStmt.executeQuery()) {
                if (rs.next() && rs.getInt(1) == 0) {
                    throw new SQLException("店铺ID不存在: " + favorites.getShopId());
                }
            }
        }

        // 插入收藏记录
        String sql = "INSERT INTO favorites (user_id, shop_id) VALUES (?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, favorites.getUserId());
            ps.setInt(2, favorites.getShopId());
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 删除收藏记录
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param shopId 店铺ID
     * @return 删除是否成功
     * @throws SQLException 如果数据库操作失败
     */
    public boolean removeFavorite(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "DELETE FROM favorites WHERE user_id = ? AND shop_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, shopId);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 查询用户收藏的店铺列表，直接返回Shop对象列表
     * @param conn 数据库连接
     * @param userId 用户ID
     * @return 收藏的店铺列表（Shop对象）
     * @throws SQLException 如果数据库操作失败
     */
    public List<Shop> getFavoriteShopsByUserId(Connection conn, int userId) throws SQLException {
        String sql = "SELECT s.* FROM favorites f INNER JOIN shop s ON f.shop_id = s.id WHERE f.user_id = ?";
        List<Shop> shopList = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Shop shop = new Shop();
                    shop.setId(rs.getInt("id"));
                    shop.setShopname(rs.getString("shopname"));
                    shop.setUserId(rs.getInt("user_id"));
                    shop.setStatus(rs.getString("status"));
                    shop.setAuditStatus(rs.getString("audit_status"));
                    shop.setSalesVolume(rs.getInt("sales_volume"));
                    shop.setPublishTime(rs.getTimestamp("publish_time"));
                    shop.setRevenue(rs.getBigDecimal("revenue"));
                    shop.setAddress(rs.getString("address"));
                    shop.setContact(rs.getString("contact"));
                    // 其他字段按Shop类补充
                    shopList.add(shop);
                }
            }
        }
        return shopList;
    }

    /**
     * 检查是否已收藏
     * @param conn 数据库连接
     * @param userId 用户ID
     * @param shopId 店铺ID
     * @return 是否已收藏
     * @throws SQLException 如果数据库操作失败
     */
    public boolean isFavorite(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM favorites WHERE user_id = ? AND shop_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, shopId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }

    public boolean existsByUserIdAndShopId(Connection conn,int userId, int shopId) throws SQLException {

        String sql = "SELECT COUNT(*) FROM favorites WHERE user_id = ? AND shop_id = ?";

        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, shopId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }
        return false;
    }
}